An Animated Guide © : Speed Merges : SAS V 9 . 1 Hashing

Hashing is one of the fastest table lookup techniques, not just in SAS®, but in any programming language. Figure 1 illustrates the concept of a table lookup and the speed advantage of SAS V9 Hashing over a format table lookup. If a programmer needs to select, from a large file, all subjects that are in a small file, hashing will likely save disk space and time. Hashing should be part of the tool kit of every programmer who deals with large files. Hashing is designed to allow a programmer to subset a large file, based on information in a small file (as is shown in figure 1). While that is the designed reason for SAS adding this new feature, hashing can also replace by merges, format lookups and IORC merges. A major benefit of hashing, is that it can perform a table lookup using two unsorted datasets. Sorting is a disk and CPU intensive process. The techniques described in the series of papers named the Animated Guide©: Speed Merges: have, as a common theme, the desire to not sort the data. This paper, on SAS V9.1 Hashing, is the fourth in the “speed merging” series and recommends that V9.1 hashing be considered for general use because of its speed and ease of use. Hashing has been the subject of much mathematical and programming research. It has been proven, in certain common conditions, to be the theoretically fastest method of accessing data. Hashing gets its speed by: 1) a being a memory resident technique, 2) having a conceptually efficient methodology and 3) being very efficient programmatically. Being a memory resident technique avoids slow disk access. The logic of the hashing algorithm gives it advantages over other lookup techniques. SAS V9 hashing is implemented via two C language Objects that are accessed through the regular SAS data step: 1) Hash (which takes care of storage/management of information in the object) and 2) Iter (which takes care of movement up and down the object) “Code your own” hashing was introduced to SAS by Dr. Paul Dorfmann. His manually-coded techniques run in any version of SAS and are very fast. However, coding of his techniques, despite his excellent examples in SAS proceedings, has been considered difficult and customizing his techniques has been rarely attempted. V9.1 Hashing, as implemented by SAS, is considered easier to use than Dr. Dorfmann’s techniques, though Dr. Dorfmann’s techniques should be considered when speed is the critical. Some Logical result 02 20 21 04 40 41 Why Learn Hashing? Fast Table Lookup!! Large file has 1,000,000 Observations Total Format Format Format Number of obs Small File as Hash Time Hashing Time as % of Time Build Lookup in Small File % of Large File (Sec.) Total Format Time Total (Sec.) Time(Sec.)Time(Sec.) 10,000 1.0% 7 75.27% 9.3 2 7.3 100,000 10.0% 9 27.69% 32.5 20.2 12.3 500,000 50.0% 12 3.90% 307.7 280 27.7 DATA STEP Small File Key SV1 SV2 SV3 02 2 2.5 2.7 09 6 7.5 3.1 04 4 4.5 4.7 Large File Key varL1 varL2 03 30 31 01 10 11 05 50 51 02 20 21 04 40 41 06 60 61 Often a By Merge And the hash object requires less memory than a format.